﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Administration.Catalog
{
    public partial class recTariffRepo : ISearchable<recTariff>
    {
        public List<recTariff> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recTariff> tt = new List<recTariff>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS  (select  ROW_NUMBER() OVER (order by {0} {1}) AS [row_number],* from( SELECT ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      recTariff.TariffID
                                                , recTariff.SubSubSectorID
                                                , recTariff.TransTypeID
                                                , recTariff.Percentage
                                                , recTariff.Value
                                                , recTariff.MinValue
                                                , recTariff.MaxValue
                                                , recTariff.Kelipatan
                                                , recTariff.ImpositionBaseID
                                                , recTariff.IsActive
                                                , recTariff.IsFormula
                                                , recTariff.CreatedDate
                                                , recTariff.CreatedBy
                                                , recTariff.UpdatedDate
                                                , recTariff.UpdatedBy
                                                , recSubSubSector.SubSubSectorName
                                                , recTransactionType.TransTypeName
                                                , recImpositionBase.ImpositonBaseName
                                                , recSubSector.SubSectorName
                                                , recSector.SectorName
                                                , recTariff.StartDate
                                                , recTariff.EndDate
                                                , recCorporateActionType.CorporateActionName
                                                , recRegistrationEmisionType.RegistrationEmisionTypeName
                                                , case
                                                    when recTransactionType.TransTypeName = 'Registrasi' then recRegistrationLabel.RegistrationLabelName
                                                    else
                                                    recTransactionType.TransTypeName
                                                    end as Label");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"         master.recTariff 
                                                    LEFT JOIN master.recSubSubSector ON recTariff.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                    LEFT JOIN master.recTransactionType ON recTariff.TransTypeID = recTransactionType.TransTypeID
                                                    LEFT JOIN master.recImpositionBase ON recTariff.ImpositionBaseID = recImpositionBase.ImpositionBaseID
                                                    LEFT JOIN master.recSubSector ON recTariff.SubSectorID = recSubSector.SubSectorID
                                                    LEFT JOIN master.recSector ON recTariff.SectorID = recSector.SectorID
                                                    LEFT JOIN master.recCorporateActionType ON recTariff.CorporateActionTypeID = recCorporateActionType.CorporateActionTypeID
                                                    LEFT JOIN master.recRegistrationEmisionType ON recTariff.RegistrationEmisionTypeID = recRegistrationEmisionType.RegistrationEmisionTypeID
                                                    LEFT JOIN master.recRegistrationLabel ON recRegistrationLabel.RegistrationLabelID = recSubSubSector.RegistrationLabelID ) a ");
                    sbQuery.AppendFormat("      {0}", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recTariff t = new recTariff(true);
                                t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                                t.SubSubSectorID = dr[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSubSectorID].ToString());
                                t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                                t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                                t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                                t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                                t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                                t.CreatedDate = dr[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.UpdatedBy].ToString();
                                t.StartDate = dr[recTariff.Fields.StartDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recTariff.Fields.StartDate].ToString());
                                t.EndDate = dr[recTariff.Fields.EndDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.EndDate].ToString());

                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recTransactionType.TransTypeName = dr["Label"] == DBNull.Value ? string.Empty : dr["Label"].ToString();
                                t.recImpositionBase.ImpositonBaseName = dr[recImpositionBase.Fields.ImpositonBaseName] == DBNull.Value ? string.Empty : dr[recImpositionBase.Fields.ImpositonBaseName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recCorporateActionType.CorporateActionName = dr[recCorporateActionType.Fields.CorporateActionName] == DBNull.Value ? string.Empty : dr[recCorporateActionType.Fields.CorporateActionName].ToString();

                                t.recRegistrationEmisionType.RegistrationEmisionTypeName = dr[recRegistrationEmisionType.Fields.RegistrationEmisionTypeName] == DBNull.Value ? string.Empty : dr[recRegistrationEmisionType.Fields.RegistrationEmisionTypeName].ToString();



                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM master.recTariff {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM master.recTariff {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM master.recTariff {1}", column, parameters);
                            break;
                        //                        case SQL.Function.Aggregate.Count:
                        //                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM master.recTariff ", column);
                        //                            sbQuery.AppendFormat(@" 
                        //                                                    LEFT JOIN master.recSubSubSector ON recTariff.SubSubSectorID = recSubSubSector.SubSubSectorID
                        //                                                    LEFT JOIN master.recTransactionType ON recTariff.TransTypeID = recTransactionType.TransTypeID
                        //                                                    LEFT JOIN master.recImpositionBase ON recTariff.ImpositionBaseID = recImpositionBase.ImpositionBaseID
                        //                                                    LEFT JOIN master.recSubSector ON recTariff.SubSectorID = recSubSector.SubSectorID
                        //                                                    LEFT JOIN master.recSector ON recTariff.SectorID = recSector.SectorID
                        //                                                    LEFT JOIN master.recCorporateActionType ON recTariff.CorporateActionTypeID = recCorporateActionType.CorporateActionTypeID
                        //                                                    LEFT JOIN master.recRegistrationEmisionType ON recTariff.RegistrationEmisionTypeID = recRegistrationEmisionType.RegistrationEmisionTypeID");
                        //                            sbQuery.AppendFormat("      {0}", parameters);
                        //                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM (select ", column);
                            sbQuery.AppendFormat(@" 
                                                    recTariff.TariffID
                                                , recTariff.SubSubSectorID
                                                , recTariff.TransTypeID
                                                , recTariff.Percentage
                                                , recTariff.Value
                                                , recTariff.MinValue
                                                , recTariff.MaxValue
                                                , recTariff.Kelipatan
                                                , recTariff.ImpositionBaseID
                                                , recTariff.IsActive
                                                , recTariff.IsFormula
                                                , recSubSubSector.SubSubSectorName
                                                , recTransactionType.TransTypeName
                                                , recImpositionBase.ImpositonBaseName
                                                , recSubSector.SubSectorName
                                                , recSector.SectorName
                                                , recTariff.StartDate
                                                , recTariff.EndDate
                                                , recCorporateActionType.CorporateActionName
                                                , recRegistrationEmisionType.RegistrationEmisionTypeName
                                                , case
                                                    when recTransactionType.TransTypeName = 'Registrasi' then recRegistrationLabel.RegistrationLabelName
                                                    else
                                                    recTransactionType.TransTypeName
                                                    end as Label FROM master.recTariff
                                                    LEFT JOIN master.recSubSubSector ON recTariff.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                    LEFT JOIN master.recTransactionType ON recTariff.TransTypeID = recTransactionType.TransTypeID
                                                    LEFT JOIN master.recImpositionBase ON recTariff.ImpositionBaseID = recImpositionBase.ImpositionBaseID
                                                    LEFT JOIN master.recSubSector ON recTariff.SubSectorID = recSubSector.SubSectorID
                                                    LEFT JOIN master.recSector ON recTariff.SectorID = recSector.SectorID
                                                    LEFT JOIN master.recCorporateActionType ON recTariff.CorporateActionTypeID = recCorporateActionType.CorporateActionTypeID
                                                    LEFT JOIN master.recRegistrationEmisionType ON recTariff.RegistrationEmisionTypeID = recRegistrationEmisionType.RegistrationEmisionTypeID
                                                    LEFT JOIN master.recRegistrationLabel ON recRegistrationLabel.RegistrationLabelID = recSubSubSector.RegistrationLabelID ) a");
                            sbQuery.AppendFormat("      {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM master.recTariff {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM master.recTariff {1}", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public recTariff GetBySubSubSectorId(int key)
        {
            recTariff t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT TariffID, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recTariff WHERE SubSubSectorID = @SubSubSectorID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recTariff();
                                t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                                t.SubSubSectorID = dr[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSubSectorID].ToString());
                                t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                                t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                                t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                                t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                                t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                                t.CreatedDate = dr[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.UpdatedBy].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recTariff GetBySubSubSectorId(int key, short transTypeId, int registrationEmisionTypeID)
        {
            recTariff t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT TariffID, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recTariff WHERE SubSubSectorID = @SubSubSectorID AND TransTypeID = @TransTypeID AND RegistrationEmisionTypeID = @RegistrationEmisionTypeID and SYSDATETIME() between StartDate and EndDate ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = key;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeId;
                    cmd.Parameters.Add("@RegistrationEmisionTypeID", SqlDbType.Int).Value = registrationEmisionTypeID;
                    try
                    {
                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            t = new recTariff();
                            t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                            t.SubSubSectorID = dr[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSubSectorID].ToString());
                            t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                            t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                            t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                            t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                            t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                            t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                            t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                            t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                            t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                            t.CreatedDate = dr[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.CreatedDate].ToString());
                            t.CreatedBy = dr[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.CreatedBy].ToString();
                            t.UpdatedDate = dr[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.UpdatedDate].ToString());
                            t.UpdatedBy = dr[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.UpdatedBy].ToString();
                        }
                        if (!dr.HasRows)
                        {
                            dr.Close();
                            cmd.Dispose();
                            SqlCommand cmd2 = conn.CreateCommand();
                            cmd2.CommandText = "SELECT TariffID, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recTariff WHERE SubSubSectorID = @SubSubSectorID AND TransTypeID = @TransTypeID AND RegistrationEmisionTypeID = @RegistrationEmisionTypeID and SYSDATETIME() > StartDate and EndDate is null ";
                            cmd2.CommandType = CommandType.Text;
                            cmd2.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = key;
                            cmd2.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeId;
                            cmd2.Parameters.Add("@RegistrationEmisionTypeID", SqlDbType.Int).Value = registrationEmisionTypeID;
                            SqlDataReader dr2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
                            while (dr2.Read())
                            {
                                t = new recTariff();
                                t.TariffID = dr2[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr2[recTariff.Fields.TariffID].ToString());
                                t.SubSubSectorID = dr2[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr2[recTariff.Fields.SubSubSectorID].ToString());
                                t.TransTypeID = dr2[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr2[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr2[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Percentage].ToString());
                                t.Value = dr2[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Value].ToString());
                                t.MinValue = dr2[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr2[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr2[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Kelipatan].ToString());
                                t.ImpositionBaseID = dr2[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr2[recTariff.Fields.ImpositionBaseID].ToString());
                                t.IsActive = dr2[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr2[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr2[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr2[recTariff.Fields.IsFormula].ToString());
                                t.CreatedDate = dr2[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr2[recTariff.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr2[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr2[recTariff.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr2[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr2[recTariff.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr2[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr2[recTariff.Fields.UpdatedBy].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recTariff GetBySubSubSectorId(int key, short transTypeId)
        {
            recTariff t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT TariffID, startdate,sectorid,subsectorid, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recTariff WHERE SubSubSectorID = @SubSubSectorID AND TransTypeID = @TransTypeID and SYSDATETIME() between StartDate and EndDate ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = key;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeId;
                    try
                    {
                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            t = new recTariff();
                            t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                            t.StartDate = dr[recTariff.Fields.StartDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recTariff.Fields.StartDate].ToString());
                            t.SectorID = dr[recTariff.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SectorID].ToString());
                            t.SubSectorID = dr[recTariff.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSectorID].ToString());
                            t.SubSubSectorID = dr[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSubSectorID].ToString());
                            t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                            t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                            t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                            t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                            t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                            t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                            t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                            t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                            t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                            t.CreatedDate = dr[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.CreatedDate].ToString());
                            t.CreatedBy = dr[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.CreatedBy].ToString();
                            t.UpdatedDate = dr[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.UpdatedDate].ToString());
                            t.UpdatedBy = dr[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.UpdatedBy].ToString();
                        }
                        if (!dr.HasRows)
                        {
                            dr.Close();
                            cmd.Dispose();
                            SqlCommand cmd2 = conn.CreateCommand();
                            cmd2.CommandText = "SELECT TariffID, startdate,sectorid,subsectorid, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recTariff WHERE SubSubSectorID = @SubSubSectorID AND TransTypeID = @TransTypeID and SYSDATETIME() > StartDate and EndDate is null ";
                            cmd2.CommandType = CommandType.Text;
                            cmd2.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = key;
                            cmd2.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeId;
                            SqlDataReader dr2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
                            while (dr2.Read())
                            {
                                t = new recTariff();
                                t.TariffID = dr2[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr2[recTariff.Fields.TariffID].ToString());
                                t.StartDate = dr2[recTariff.Fields.StartDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr2[recTariff.Fields.StartDate].ToString());
                                t.SectorID = dr2[recTariff.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr2[recTariff.Fields.SectorID].ToString());
                                t.SubSectorID = dr2[recTariff.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr2[recTariff.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr2[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr2[recTariff.Fields.SubSubSectorID].ToString());
                                t.TransTypeID = dr2[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr2[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr2[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Percentage].ToString());
                                t.Value = dr2[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Value].ToString());
                                t.MinValue = dr2[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr2[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr2[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Kelipatan].ToString());
                                t.ImpositionBaseID = dr2[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr2[recTariff.Fields.ImpositionBaseID].ToString());
                                t.IsActive = dr2[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr2[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr2[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr2[recTariff.Fields.IsFormula].ToString());
                                t.CreatedDate = dr2[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr2[recTariff.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr2[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr2[recTariff.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr2[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr2[recTariff.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr2[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr2[recTariff.Fields.UpdatedBy].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recTariff GetBySubSubSectorId(int key, short transTypeId, bool Isactive)
        {
            recTariff t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT TariffID, SectorID, SubSectorID, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, StartDate, EndDate, CorporateActionTypeID, RegistrationEmisionTypeID FROM master.recTariff WHERE SubSubSectorID = @SubSubSectorID AND TransTypeID = @TransTypeID and Isactive=@isactive";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = key;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeId;
                    cmd.Parameters.Add("@isactive", SqlDbType.Bit).Value = Isactive;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recTariff();
                                t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                                t.SectorID = dr[recTariff.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recTariff.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSubSectorID].ToString());
                                t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                                t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                                t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                                t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                                t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                                t.CreatedDate = dr[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.UpdatedBy].ToString();
                                t.StartDate = dr[recTariff.Fields.StartDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recTariff.Fields.StartDate].ToString());
                                t.EndDate = dr[recTariff.Fields.EndDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.EndDate].ToString());
                                t.CorporateActionTypeID = dr[recTariff.Fields.CorporateActionTypeID] == DBNull.Value ? (int?)null : int.Parse(dr[recTariff.Fields.CorporateActionTypeID].ToString());
                                t.RegistrationEmisionTypeID = dr[recTariff.Fields.RegistrationEmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.RegistrationEmisionTypeID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recTariff GetBySubSubSectorIdForCorporateAction(int subSubSectorId, short transTypeId, bool Isactive, int corporateActionId)
        {
            recTariff t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT TariffID, SectorID, SubSectorID, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, StartDate, EndDate, CorporateActionTypeID, RegistrationEmisionTypeID FROM master.recTariff WHERE SubSubSectorID = @SubSubSectorID AND TransTypeID = @TransTypeID and Isactive=@isactive and CorporateActionTypeID = @CorporateActionTypeID ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subSubSectorId;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeId;
                    cmd.Parameters.Add("@isactive", SqlDbType.Bit).Value = Isactive;
                    cmd.Parameters.Add("@CorporateActionTypeID", SqlDbType.Int).Value = corporateActionId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recTariff();
                                t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                                t.SectorID = dr[recTariff.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recTariff.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSubSectorID].ToString());
                                t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                                t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                                t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                                t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                                t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                                t.CreatedDate = dr[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.UpdatedBy].ToString();
                                t.StartDate = dr[recTariff.Fields.StartDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recTariff.Fields.StartDate].ToString());
                                t.EndDate = dr[recTariff.Fields.EndDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.EndDate].ToString());
                                t.CorporateActionTypeID = dr[recTariff.Fields.CorporateActionTypeID] == DBNull.Value ? (int?)null : int.Parse(dr[recTariff.Fields.CorporateActionTypeID].ToString());
                                t.RegistrationEmisionTypeID = dr[recTariff.Fields.RegistrationEmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.RegistrationEmisionTypeID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public recTariff GetBySubSubSectorIdForRegistrationEmisi(int subSubSectorId, short transTypeId, bool Isactive, int registrationEmisionTypeId)
        {
            recTariff t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT TariffID, SectorID, SubSectorID, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, StartDate, EndDate, CorporateActionTypeID, RegistrationEmisionTypeID FROM master.recTariff WHERE SubSubSectorID = @SubSubSectorID AND TransTypeID = @TransTypeID and Isactive=@isactive and RegistrationEmisionTypeID = @RegistrationEmisionTypeID ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subSubSectorId;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeId;
                    cmd.Parameters.Add("@isactive", SqlDbType.Bit).Value = Isactive;
                    cmd.Parameters.Add("@RegistrationEmisionTypeID", SqlDbType.Int).Value = registrationEmisionTypeId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recTariff();
                                t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                                t.SectorID = dr[recTariff.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recTariff.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSubSectorID].ToString());
                                t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                                t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                                t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                                t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                                t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                                t.CreatedDate = dr[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.UpdatedBy].ToString();
                                t.StartDate = dr[recTariff.Fields.StartDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recTariff.Fields.StartDate].ToString());
                                t.EndDate = dr[recTariff.Fields.EndDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.EndDate].ToString());
                                t.CorporateActionTypeID = dr[recTariff.Fields.CorporateActionTypeID] == DBNull.Value ? (int?)null : int.Parse(dr[recTariff.Fields.CorporateActionTypeID].ToString());
                                t.RegistrationEmisionTypeID = dr[recTariff.Fields.RegistrationEmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.RegistrationEmisionTypeID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recTariff GetByCorporateActionType(short transTypeID, int corporateActionTypeID, int subSubSectorID)
        {
            recTariff t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = @"
                                        SELECT 
                                            TariffID, TransTypeID, Percentage, Value, MinValue, ImpositionBaseID,
                                            MaxValue, Kelipatan, IsActive, IsFormula, CorporateActionTypeID 
                                        FROM 
                                            master.recTariff 
                                        WHERE (CorporateActionTypeID = @CorporateActionTypeID AND TransTypeID = @TransTypeID AND SubSubSectorID = @SubSubSectorID and SYSDATETIME() between StartDate and EndDate ) ";

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@CorporateActionTypeID", SqlDbType.Int).Value = corporateActionTypeID;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeID;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subSubSectorID;

                    try
                    {
                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            t = new recTariff();
                            t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                            t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                            t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                            t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                            t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                            t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                            t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                            t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                            t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                            t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                            t.CorporateActionTypeID = dr[recTariff.Fields.CorporateActionTypeID] == DBNull.Value ? (int?)null : int.Parse(dr[recTariff.Fields.CorporateActionTypeID].ToString());
                        }
                        if (!dr.HasRows)
                        {
                            dr.Close();
                            cmd.Dispose();
                            SqlCommand cmd2 = conn.CreateCommand();
                            cmd2.CommandText = @"
                                        SELECT 
                                            TariffID, TransTypeID, Percentage, Value, MinValue, ImpositionBaseID,
                                            MaxValue, Kelipatan, IsActive, IsFormula, CorporateActionTypeID 
                                        FROM 
                                            master.recTariff 
                                        WHERE (CorporateActionTypeID = @CorporateActionTypeID AND TransTypeID = @TransTypeID AND SubSubSectorID = @SubSubSectorID and SYSDATETIME() > StartDate and EndDate is null ) ";

                            cmd2.CommandType = CommandType.Text;
                            cmd2.Parameters.Add("@CorporateActionTypeID", SqlDbType.Int).Value = corporateActionTypeID;
                            cmd2.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = transTypeID;
                            cmd2.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subSubSectorID;
                            SqlDataReader dr2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
                            while (dr2.Read())
                            {
                                t = new recTariff();
                                t.TariffID = dr2[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr2[recTariff.Fields.TariffID].ToString());
                                t.ImpositionBaseID = dr2[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr2[recTariff.Fields.ImpositionBaseID].ToString());
                                t.TransTypeID = dr2[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr2[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr2[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Percentage].ToString());
                                t.Value = dr2[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Value].ToString());
                                t.MinValue = dr2[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr2[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr2[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr2[recTariff.Fields.Kelipatan].ToString());
                                t.IsActive = dr2[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr2[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr2[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr2[recTariff.Fields.IsFormula].ToString());
                                t.CorporateActionTypeID = dr2[recTariff.Fields.CorporateActionTypeID] == DBNull.Value ? (int?)null : int.Parse(dr2[recTariff.Fields.CorporateActionTypeID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }


        public List<recTariff> GetListById(int tariffId)
        {
            List<recTariff> tt = new List<recTariff>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT TariffID, SectorID, SubSectorID, SubSubSectorID, TransTypeID, Percentage, Value, MinValue, MaxValue, Kelipatan, ImpositionBaseID, IsActive, IsFormula, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, StartDate, EndDate, CorporateActionTypeID, RegistrationEmisionTypeID FROM master.recTariff where TariffID = @TariffID ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@TariffID", SqlDbType.Int).Value = tariffId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recTariff t = new recTariff();
                                t.TariffID = dr[recTariff.Fields.TariffID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.TariffID].ToString());
                                t.SectorID = dr[recTariff.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recTariff.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recTariff.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recTariff.Fields.SubSubSectorID].ToString());
                                t.TransTypeID = dr[recTariff.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTariff.Fields.TransTypeID].ToString());
                                t.Percentage = dr[recTariff.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Percentage].ToString());
                                t.Value = dr[recTariff.Fields.Value] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Value].ToString());
                                t.MinValue = dr[recTariff.Fields.MinValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MinValue].ToString());
                                t.MaxValue = dr[recTariff.Fields.MaxValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.MaxValue].ToString());
                                t.Kelipatan = dr[recTariff.Fields.Kelipatan] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recTariff.Fields.Kelipatan].ToString());
                                t.ImpositionBaseID = dr[recTariff.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.ImpositionBaseID].ToString());
                                t.IsActive = dr[recTariff.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsActive].ToString());
                                t.IsFormula = dr[recTariff.Fields.IsFormula] == DBNull.Value ? false : bool.Parse(dr[recTariff.Fields.IsFormula].ToString());
                                t.CreatedDate = dr[recTariff.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recTariff.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recTariff.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recTariff.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recTariff.Fields.UpdatedBy].ToString();
                                t.StartDate = dr[recTariff.Fields.StartDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recTariff.Fields.StartDate].ToString());
                                t.EndDate = dr[recTariff.Fields.EndDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recTariff.Fields.EndDate].ToString());
                                t.CorporateActionTypeID = dr[recTariff.Fields.CorporateActionTypeID] == DBNull.Value ? (int?)null : int.Parse(dr[recTariff.Fields.CorporateActionTypeID].ToString());
                                t.RegistrationEmisionTypeID = dr[recTariff.Fields.RegistrationEmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recTariff.Fields.RegistrationEmisionTypeID].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }		

    }
}
